﻿using Blm.PgsqlDriver.Helpers;
using Blm.Utils.Extensions;
using Language.Resource;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Localization;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using VisionCloud.Core.Model.Ms;
using VisionCloud.Domain.DtoModel.Applet.RequestDto;
using VisionCloud.Domain.DtoModel.Applet.ResponseDto;
using VisionCloud.Domain.DtoModel.Ms;
using VisionCloud.Domain.Repository.Interface.Ms;

namespace VisionCloud.Domain.Repository.Implement.Ms
{
    public class CSMSTaskRepository : BaseRepository<CSMSTask>, ICSMSTaskRepository
    {
        public IStringLocalizer<RepositoryResource> Localizer { get; set; }

        /// <summary>
        /// 获取任务总报告
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        public GetSummaryReportRspDto GetSummaryReport(GetSummaryReportRqDto request)
        {
            // 数据里存的阶段类型没有统一，导致这里查询的入参麻烦。先拆分解决，不是四个字就另外整去吧。
            if (request.MeasureStep.Length != 4)
                throw new ArgumentException(Localizer["YB0227"].Value);
            string projectStep = request.MeasureStep.Substring(0, 2);
            string measureStep = request.MeasureStep.Substring(2, 2);
            var result = Db.Set<CSMSTask>().Where(x => x.ProjectId == new Guid(request.ProjectId)
            && x.ProjectStage.Contains(projectStep)
            && x.TaskName.Contains(measureStep)
            && x.BuildingName == request.BuildingName
            && x.FloorName == request.FloorName)
                .Select(x => new GetSummaryReportRspDto
                {
                    TaskId = x.TaskId,
                    ResultDataFilePath = x.ResultDataFilePath,
                    TaskName = x.TaskName
                })
                .Join(Db.Set<MsTask>().Where(x => x.ProjectId == new Guid(request.ProjectId) && x.TaskType == request.ProjectType),
                x => x.TaskId, y => y.TaskId, (x, y) =>
                new GetSummaryReportRspDto
                {
                    TaskId = x.TaskId,
                    ResultDataFilePath = x.ResultDataFilePath,
                    TaskName = x.TaskName
                }).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// 获取某段时间内有上传数据的设备
        /// </summary>
        /// <param name="start_time"></param>
        /// <param name="end_time"></param>
        /// <returns></returns>
        public async Task<List<DeviceProjectPushDto>> GetLastUploadTaskDeviceAsync(DateTime start_time, DateTime end_time)
        {
            var sql = $@"select * from (
                        SELECT (row_number() over (PARTITION BY a.""DeviceGuid"" ORDER BY a.""ReportUploadDateTime"" desc )) rn,
                        a.""DeviceGuid"",a.""ReportUploadDateTime"",b.""DeviceNumber"",b.""IsActive"",b.""DeviceSoftwareVersion"",c.""Name"" as ""ProjectName""
                        FROM ""CSMSTask"" a
                        inner join ""SysDevice"" b on a.""DeviceGuid"" = b.""DeviceGuid""
                        inner join ""MsProject"" c on a.""ProjectId"" = c.""ProjectId""
                        where a.""ReportUploadDateTime"" >= '{start_time.ToDateTimeStandardString()}' and a.""ReportUploadDateTime"" < '{end_time.ToDateTimeStandardString()}'
                        and a.""IsDeleted"" = false and a.""IsDeleteReport"" = false
                        ) tb where rn = 1";

            var list = await PgSqlHelper.QueryListAsync<DeviceProjectPushDto>(CommandType.Text, sql);

            return list;
        }

        /// <summary>
        /// 获取指定设备上传的数据
        /// </summary>
        /// <param name="deviceIds"></param>
        /// <returns></returns>
        public async Task<List<DeviceProjectPushDto>> GetLastUploadTaskDeviceAsync(List<Guid> deviceIds)
        {
            List<DeviceProjectPushDto> list = new List<DeviceProjectPushDto>();
            if (deviceIds == null || deviceIds.Count == 0)
                return list;

            var idString = string.Join(",", deviceIds.Select(p => $"'{p}'"));
            var sql = $@"select * from (
                        SELECT (row_number() over (PARTITION BY a.""DeviceGuid"" ORDER BY a.""ReportUploadDateTime"" desc )) rn,
                        a.""DeviceGuid"",a.""ReportUploadDateTime"",b.""DeviceNumber"",b.""IsActive"",b.""DeviceSoftwareVersion"",c.""Name"" as ""ProjectName""
                        FROM ""CSMSTask"" a
                        inner join ""SysDevice"" b on a.""DeviceGuid"" = b.""DeviceGuid""
                        inner join ""MsProject"" c on a.""ProjectId"" = c.""ProjectId""
                        where a.""DeviceGuid"" in ({idString}) and a.""IsDeleted"" = false and a.""IsDeleteReport"" = false
                        ) tb where rn = 1";

            list = await PgSqlHelper.QueryListAsync<DeviceProjectPushDto>(CommandType.Text, sql);

            return list;
        }

        /// <summary>
        /// 查询设备的累计测量面积和累计作业天数
        /// </summary>
        /// <param name="deviceIds"></param>
        /// <param name="end_time"></param>
        /// <returns></returns>
        public async Task<List<DeviceStitisticsDto>> GetDeviceStitisticsAsync(List<Guid> deviceIds, DateTime end_time)
        {
            List<DeviceStitisticsDto> list = new List<DeviceStitisticsDto>();
            if (deviceIds == null || deviceIds.Count == 0)
                return list;

            var idStr = string.Join(",", deviceIds.Select(p => $"'{p}'"));
            var sql = $@"SELECT t2.""DeviceGuid"" ""Id"",ROUND(SUM(t5.""PointAvgArea"" * t1.""CompletedPointCount""),2) ""TotalAreas"",COUNT( DISTINCT concat (t1.""Year"", t1.""Month"", t1.""Day"") ) AS ""WorkDays""
                        FROM ""vw_personnel_efficiency"" t1
                        INNER JOIN ""CSMSTask"" t2 on t2.""TaskId"" = t1.""TaskId""
                        INNER JOIN ""SysDevice"" t3 on t3.""DeviceGuid"" = t2.""DeviceGuid"" AND t3.""IsDeleted"" = false
                        INNER JOIN ""MsProject"" t4 on t4.""ProjectId"" = t1.""ProjectGuid"" AND t4.""IsDeleted"" = false
                        INNER JOIN ""vw_average_point_area"" t5 on t5.""HouseMapId"" = t2.""HouseMapId""
                        where t2.""DeviceGuid"" in ({idStr}) and t2.""ReportUploadDateTime"" < '{end_time.ToDateTimeStandardString()}'
                        GROUP BY t2.""DeviceGuid""";

            list = await PgSqlHelper.QueryListAsync<DeviceStitisticsDto>(CommandType.Text, sql);

            return list;
        }

        /// <summary>
        /// 获取项目关联的设备
        /// </summary>
        /// <param name="projectId"></param>
        /// <returns></returns>
        public async Task<List<Guid>> GetProjectRelevanceDeviceAsync(Guid projectId)
        {
            var list = await Db.CSMSTasks.Where(s => s.ProjectId == projectId).Select(p => p.DeviceGuid).ToListAsync();

            return list;
        }

        /// <summary>
        /// 获取某段时间内设备上传的每日数据统计
        /// </summary>
        /// <param name="start_time"></param>
        /// <param name="end_time"></param>
        /// <returns></returns>
        public async Task<List<DeviceMeasureDayStatModel>> GetDeviceDayMeasureStatAsync(DateTime start_time, DateTime end_time)
        {
            string sql = $@"SELECT t3.""DeviceNumber"",t1.""CompletedDate"",t4.""Name"" as ""ProjectName"",ROUND(Sum(t1.""CompletedPointCount"" * t5.""PointAvgArea""),2) as ""TotalAreas""
                            FROM ""vw_personnel_efficiency_new"" t1
                            INNER JOIN ""CSMSTask"" t2 on t2.""TaskId"" = t1.""TaskId""
                            INNER JOIN ""SysDevice"" t3 on t3.""DeviceGuid"" = t2.""DeviceGuid"" AND t3.""IsDeleted"" = false
                            INNER JOIN ""MsProject"" t4 on t4.""ProjectId"" = t1.""ProjectGuid"" AND t4.""IsDeleted"" = false
                            INNER JOIN ""vw_average_point_area"" t5 on t5.""HouseMapId"" = t2.""HouseMapId""
                            where t2.""ReportUploadDateTime"" >= '{start_time.ToDateTimeStandardString()}' and t2.""ReportUploadDateTime"" < '{end_time.ToDateTimeStandardString()}'
                            GROUP BY t3.""DeviceNumber"",t1.""CompletedDate"",t4.""Name""";

            var list = await PgSqlHelper.QueryListAsync<DeviceMeasureDayStatModel>(CommandType.Text, sql);

            return list;
        }

        /// <summary>
        /// 根据项目Id和楼栋名获取已测量楼层信息
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public async Task<List<WxProjectFloorResponse>> WxGetReportData(WxProjectBuildingDto query)
        {
            List<NpgsqlParameter> paraList = new List<NpgsqlParameter>
            {
                new NpgsqlParameter()
                {
                    ParameterName = "ProjectId",
                    Value = query.ProjectId,
                },
                new NpgsqlParameter()
                {
                    ParameterName = "BuildingName",
                    Value = query.BuildingName,
                }
            };

            string sql = $@"SELECT DISTINCT
	                        T1.""ProjectId"",
                            T2.""Name"" AS ""ProjectName"",
                            T1.""BuildingName"",
	                        T1.""FloorName""
                        FROM
                            ""CSMSTask"" T1
                            INNER JOIN ""MsProject"" T2 ON T1.""ProjectId"" = T2.""ProjectId""
                            INNER JOIN ""SysOrg"" T3 ON T2.""CompanyGuid"" = T3.""Id""
                        WHERE
                            T1.""IsDeleted"" = FALSE
                        AND T1.""ProjectId""=@ProjectId
                        AND T1.""BuildingName""=@BuildingName
                        AND T3.""Id""=@CompanyId";

            var list = await PgSqlHelper.QueryListAsync<WxProjectFloorResponse>(CommandType.Text, sql, false, paraList.ToArray());

            return list;
        }

        /// <summary>
        /// 获取任务的结果存储目录
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns></returns>
        public async ValueTask<string> GetResultDataFilePathAsync(Guid taskId)
        {
            return await Db.CSMSTasks.Where(p => p.TaskId == taskId && !p.IsDeleted).Select(p => p.ResultDataFilePath).FirstOrDefaultAsync();
        }

        /// <summary>
        /// 获取下载的数据报告列表
        /// </summary>
        /// <param name="ids"></param>
        /// <param name="dirPath"></param>
        /// <returns></returns>
        public async ValueTask<List<FileDownloadDto>> GetDownloadReportsAsync(List<Guid> ids, string dirPath)
        {
            List<FileDownloadDto> reportList = new List<FileDownloadDto>();
            if (ids.IsEmpty())
                return reportList;

            var list = await (from cs in Db.CSMSTasks
                              join t in Db.MsTasks on cs.TaskId equals t.TaskId
                              where ids.Contains(cs.TaskId) && cs.IsDeleteReport == false && t.MeasureType == "4"
                              select new { cs.TaskId, cs.ResultDataFilePath, t.TaskName, t.MeasureType }).AsNoTracking().ToListAsync();

            foreach (var item in list)
            {
                reportList.Add(new FileDownloadDto
                {
                    TaskId = item.TaskId,
                    MeasureType = item.MeasureType.ToInt32(),
                    ReportOldDir = item.ResultDataFilePath,
                    ReportDownloadDir = $"{dirPath}/{item.TaskName}_{item.TaskId}",
                    ZipFilePath = $"{dirPath}/{item.TaskName}_{item.TaskId}.zip"
                });
            }

            return reportList;
        }
    }
}